﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RegistroOTResumenMultipleRep')
	BEGIN
		DROP  Procedure  RegistroOTResumenMultipleRep
	END
GO 

CREATE PROCEDURE [dbo].[RegistroOTResumenMultipleRep]
	(	
		@EmpresaId	AS INT,		
		@NumeroOT	AS VARCHAR(30) = NULL,
		@Status		AS VARCHAR(1) = NULL,
		@BeginDate	AS DATETIME = NULL,
		@EndDate	AS DATETIME = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON

	CREATE TABLE #OTIds	(Id INT)

	IF @BeginDate IS NULL	
		INSERT INTO #OTIds
		SELECT Id 
		FROM Lit_OT
		WHERE EmpresaId = @EmpresaId
		  AND NumeroOT = ISNULL(@NumeroOT, NumeroOT)
		  AND Estado = ISNULL(@Status, Estado)
	ELSE
		INSERT INTO #OTIds
		SELECT Id 
		FROM Lit_OT
		WHERE EmpresaId = @EmpresaId
		  AND NumeroOT = ISNULL(@NumeroOT, NumeroOT)
		  AND Estado = ISNULL(@Status, Estado)
		  AND FechaInicial BETWEEN @BeginDate AND @EndDate
	

	DECLARE @OTs Table
		(NumeroOT	VARCHAR(30),
		Entrega	INT,
		EmpresaNombre VARCHAR (50),
		FechaInicial DATETIME,
		FechaFinal DATETIME,
		Estado VARCHAR(20),
		Cantidad decimal(10, 2),
		CantidadProducida decimal(10, 2),
		MontoManoDeObra decimal(10, 2),
		MontoMateriales decimal(10, 2),
		MontoGastosDirectos decimal(10, 2),
		MontoGastosIndirectos decimal(10, 2),
		MontoMaquinasUtilizadas decimal(10, 2))
	
	DECLARE @OTDetalle Table
		(EntregaId	INT,
		 Total		DECIMAL(10,2))

	DECLARE @OTDetalleSum Table
		(EntregaId	INT,
		 Total		DECIMAL(10,2))

	DECLARE @OTMateriales Table
		(EntregaId	INT,
		 Total		DECIMAL(10,2))

	DECLARE @OTGastosDirectos TABLE
		(EntregaId	INT,
		Total		DECIMAL(10,2))

	DECLARE @OTGastosIndirectos TABLE
		(EntregaId	INT,
		Total		DECIMAL(10,2))

	DECLARE @OTGastosIndirectosSum TABLE
		(EntregaId	INT,
		Total		DECIMAL(10,2))

	DECLARE @OTMaquinasUtilizadas TABLE
		(EntregaId	INT,
		Total		DECIMAL(10,2))

	-- OT Mano de Obra Utilizada
	INSERT INTO @OTDetalle
	SELECT o.OTEntregaId, SUM(  (ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0)) 
							+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5)
							+(ISNULL(HorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_Operario op ON o.operarioId = op.id
	INNER JOIN Lit_Entrega e ON e.Id = o.OTEntregaId
	WHERE e.OTId IN (SELECT Id FROM #OTIds)	  
	GROUP BY o.OTEntregaId
	UNION
	SELECT o.OTId, SUM(  (ISNULL(HorasRegular,0)* ISNULL(CostoAyudantexHora,0))
						+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) 
						+(ISNULL(HorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
	WHERE o.OTId IN (SELECT Id FROM #OTIds)  
	GROUP BY o.OTId
	
	INSERT INTO @OTDetalleSum
	SELECT Id, SUM(Total)
	FROM @OTDetalle
	GROUP BY Id

	-- OT Materiales Utilizados
	INSERT INTO @OTMateriales
	SELECT m.OTId, SUM(CostoArticuloTotal)
	FROM Lit_OTMaterial m
	INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
	WHERE m.OTId IN (SELECT Id FROM #OTIds)
	AND a.Emp_Id = @empresaId
	GROUP BY m.OTId

	-- OT Gastos Directos
	INSERT INTO @OTGastosDirectos
	SELECT otg.OTId, SUM(otg.Monto)
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	WHERE otg.OTId IN (SELECT Id FROM #OTIds) 
	AND g.Tipo = 'D'
	GROUP BY otg.OTId

	-- OT Gastos Indirectos
	INSERT INTO @OTGastosIndirectos
	SELECT otg.OTId, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	WHERE otg.OTId IN (SELECT Id FROM #OTIds)  
	AND g.Tipo = 'I'
	UNION
	SELECT Id, ISNULL(ManoObraIndirecta,0)
	FROM Lit_OrdenTrabajo
	WHERE Id IN (SELECT Id FROM #OTIds)
	AND ManoObraIndirecta IS NOT NULL
	AND ManoObraIndirecta > 0

	INSERT INTO @OTGastosIndirectosSum
	SELECT Id, SUM(Total)
	FROM @OTGastosIndirectos
	GROUP BY Id

	-- OT Maquinas Utilizadas
	INSERT INTO @OTMaquinasUtilizadas
	SELECT o.OTId, SUM(CostoMaquina)
	FROM Lit_OTDetalle o
	WHERE OTId IN (SELECT Id FROM #OTIds)
	GROUP BY o.OTId

	INSERT INTO @RegistroOTs 
	SELECT	o.NumeroOT, 
			e.Nombre AS EmpresaNombre, 
			FechaInicial, 
			FechaFinal, 
		    CASE o.Estado
				WHEN 'A' THEN 'EN PROCESO'
				WHEN 'T' THEN 'FINALIZADA'
			END AS Estado, 
			ISNULL(o.UnidadesSolicitadas,0), 
			ISNULL(o.UnidadesProducidas,0),
			ISNULL(mo.Total, 0),
			ISNULL(m.Total,0),
			ISNULL(gd.Total, 0),
			ISNULL(gi.Total, 0),
			ISNULL(mu.Total, 0)
	FROM lit_ordenTrabajo o 
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	LEFT JOIN @OTDetalleSum mo ON mo.Id = o.Id
	LEFT JOIN @OTMateriales m ON m.Id = o.Id
	LEFT JOIN @OTGastosDirectos gd ON gd.Id = o.Id
	LEFT JOIN @OTGastosIndirectosSum gi ON gi.Id = o.Id
	LEFT JOIN @OTMaquinasUtilizadas mu ON mu.Id = o.Id
	WHERE o.Id IN (SELECT Id FROM #OTIds)
	
	SELECT *, MontoManoDeObra + MontoMateriales + MontoGastosDirectos + MontoGastosIndirectos + MontoMaquinasUtilizadas As MontoTotal
	FROM @RegistroOTs
           	          
	SET NOCOUNT OFF
END
GO